﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using ExcelLibrary.SpreadSheet;

namespace RepairChecker
{

    public static class Entity
    {
        public static string connectionString =
            ConfigurationManager.ConnectionStrings["ConnectionString_Local"].ConnectionString;

        public enum JobType
        {
            Repair = 1,
            Maintain = 2,
            Check = 3
        }

        static SqlConnection conn = new SqlConnection(connectionString);

        public static int DeleteJob(int formId)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("DeleteJob", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@FormId", formId);

            using (conn)
            {
                conn.Open();
                int ret =
                    cmd.ExecuteNonQuery();

                return ret;
            }
        }

        //public static string OutputMonthlyReport(string startDate, string endDate, out System.IO.MemoryStream ms) 
        //{
        
        //}


        public static string OutputWeeklyReport(string startDate ,string endDate ,out System.IO.MemoryStream ms)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("OutputMonthlyReport", conn);
            SqlDataReader reader;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@StartDate", startDate);
            cmd.Parameters.AddWithValue("@EndDate", endDate);

            List<Fault> faults = Entity.GetAllFaults(true);
            List<MonthlyReportItem> report = new List<MonthlyReportItem>();
            MonthlyReportItem item;
            using (conn)
            {
                conn.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    item = new MonthlyReportItem();
                    item.ArriveTime = reader["ArriveTime"].ToString();
                    item.CrewName = reader["CrewName"].ToString();
                    item.FaultIdString = reader["FaultIdString"].ToString();
                    item.FinishtTime = reader["FinishTime"].ToString();
                    item.JobDate = reader["Date"].ToString();
                    item.Office = reader["OfficeName"].ToString();
                    item.RepairType = reader["JobType"].ToString();

                    foreach (Fault f in faults)
                    {
                        if (item.FaultIdString.Contains(f.FaultId.ToString()))
                        {
                            item.Faults += (string.IsNullOrEmpty(item.Faults) ? "" : ",") + f.FaultName;
                        }
                    }


                    report.Add(item);
                }
            }
            //Workbook workbook = Workbook.Load("d:/text.xls");

            List<Crew> crews = Entity.GetAllCrews();
            Workbook book = new Workbook();
            
            foreach (Crew c in crews)
            {
                Worksheet sheet = new Worksheet(c.CrewName);
                int line = 0;
                var items = from r in report where r.CrewName == c.CrewName select r;
                foreach (MonthlyReportItem r in items)
                {

                    sheet.Cells[line, 0] = new Cell(r.JobDate);
                    sheet.Cells[line, 1] = new Cell(r.CrewName);
                    sheet.Cells[line, 2] = new Cell(r.Office);
                    sheet.Cells[line, 3] = new Cell(r.RepairType);
                    sheet.Cells[line, 4] = new Cell(r.ArriveTime);
                    sheet.Cells[line, 5] = new Cell(r.FinishtTime);
                    sheet.Cells[line, 6] = new Cell(r.Faults);
                    line++;
                }
                book.Worksheets.Add(sheet);

            }
            ms = new System.IO.MemoryStream();
            book.SaveToStream(ms);
            
            
            return "-1";
           

            ////foreach (MonthlyReportItem m  in report)
            ////{
            ////    foreach (Worksheet s in book.Worksheets)
            ////    {
            ////        if (s.Name==m.Crew)
            ////        {
            ////            sheet = s;
            ////            break;
            ////        }
            ////    }

                
            ////}


        }

        public static int JobDone(int formId, DateTime arriveTime, DateTime finishTime, string formNo, string faultIdString) 
        {
            //int ret;
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("JobDone", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@FormId", formId);
            cmd.Parameters.AddWithValue("@ArriveDate", arriveTime);
            cmd.Parameters.AddWithValue("@FaultIdString", faultIdString);

            if (finishTime!=DateTime.MinValue)cmd.Parameters.AddWithValue("@FinishDate", finishTime);

            cmd.Parameters.AddWithValue("@FormNo", formNo);
            cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.BigInt);
            cmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
            using (conn)
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            return (int)cmd.Parameters["@RETURN_VALUE"].Value;
        }

        public static int SetNotified(int formId, int crewId) 
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("SetNotified", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@FormId", formId);
            cmd.Parameters.AddWithValue("@CrewId", crewId);
            cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.BigInt);
            cmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;

            using (conn)
            {
                conn.Open();
                cmd.ExecuteNonQuery();

            }
            return (int)cmd.Parameters["@RETURN_VALUE"].Value;
        }

        public static List<Job> GetJobs(int crewId, int districtId, bool hideNotified)
        {
            return GetJobs(crewId, districtId, (hideNotified ? 1 : 0),1,"",0,DateTime.Now,DateTime.Now);
        }

        public static List<Job> GetJobs(int crewId, int districtId,int showMode,int jobTypeId,string faultIdString,int dateType,DateTime dateStart,DateTime dateEnd)
        {
            List<Job> ret = new List<Job>();
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("GetJobs", conn);
            SqlDataReader reader;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@CrewId", crewId);
            cmd.Parameters.AddWithValue("@DistrictId", districtId);
            cmd.Parameters.AddWithValue("@ShowMode", showMode);
            cmd.Parameters.AddWithValue("@JobTypeId", jobTypeId);
            cmd.Parameters.AddWithValue("@FaultIdString", faultIdString);

            cmd.Parameters.AddWithValue("@DateType", dateType);

            if (dateType>0)
            {
                cmd.Parameters.AddWithValue("@DateStart", dateStart.Date.ToString());
                cmd.Parameters.AddWithValue("@DateEnd", dateEnd.Date.AddDays(1).AddSeconds(-1).ToString());
            }
            //cmd.Parameters.AddWithValue("@CallInDate", callInDate);
            using (conn)
            {

                conn.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Job job = new Job();
                    job.DistrictId = (int)reader["DistrictId"];
                    job.DistrictName = reader["DistrictName"].ToString();
                    job.OfficeId = (int)reader["OfficeId"];
                    job.OfficeName = reader["OfficeName"].ToString();
                    job.FaultIdString = (string)reader["FaultIdString"];
                    //job.FaultName = reader["FaultName"].ToString();
                    job.NeedMaintaince = (bool)reader["Maintained"];
                    job.CallInDate = reader["CallInDate"].ToString();
                    job.FormId = (int)reader["FormId"];
                    job.NotifyDate = reader["NotifyDate"].ToString();
                    job.ArriveDate = reader["ArriveDate"].ToString();
                    job.FinishDate = reader["FinishDate"].ToString();
                    job.CrewId = (int)reader["CrewId"];
                    job.CrewName = reader["CrewName"].ToString();
                    job.JobStatus = (int)reader["JobStatusId"];
                    job.JobType = reader["JobType"].ToString();
                    job.Memo = reader["Memo"].ToString();
                    //job.DelegateCrewId = int.Parse(reader["DelegateCrewId"].ToString());
                    //job.DelegateCrewName = reader["DelegateCrewName"].ToString();
                    ret.Add(job);
                }
            }
            return ret;
        }


        /*
        public static List<Job> GetUnfinishedJob(int crewId, int districtId, bool hideNotified, int showMaintaince) 
        {
            return GetUnfinishedJob(crewId, districtId, (hideNotified ? 1 : 0), showMaintaince);
        }

        public static List<Job> GetUnfinishedJob(int crewId, int districtId,int showMode,int showMaintaince) 
        {
            List<Job> ret = new List<Job>();
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("GetUnfinishedJob", conn);
            SqlDataReader reader;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@CrewId", crewId);
            cmd.Parameters.AddWithValue("@DistrictId", districtId);
            cmd.Parameters.AddWithValue("@ShowMode",showMode);
            cmd.Parameters.AddWithValue("@ShowMaintaince", showMaintaince);
            //cmd.Parameters.AddWithValue("@CallInDate", callInDate);
            using (conn)
            {

                conn.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Job job=new Job();
                    job.DistrictId=(int)reader["DistrictId"];
                    job .DistrictName=reader["DistrictName"].ToString();
                    job.OfficeId=(int)reader["OfficeId"];
                    job.OfficeName=reader["OfficeName"].ToString();
                    job.FaultId=(int)reader["FaultId"];
                    job.FaultName=reader["FaultName"].ToString();
                    job.NeedMaintaince=(bool)reader["Maintained"];
                    job.CallInDate = reader["CallInDate"].ToString();
                    job.FormId = (int)reader["FormId"];
                    job.NotifyDate = reader["NotifyDate"].ToString();
                    job.ArriveDate = reader["ArriveDate"].ToString();
                    job.CrewId = (int)reader["CrewId"];
                    job.CrewName = reader["CrewName"].ToString();
                    job.JobStatus = (int)reader["JobStatusId"];
                    job.JobType = reader["JobType"].ToString();
                    job.Memo = reader["Memo"].ToString();
                    //job.DelegateCrewId = int.Parse(reader["DelegateCrewId"].ToString());
                    //job.DelegateCrewName = reader["DelegateCrewName"].ToString();
                    ret.Add(job);
                }
            }
            return ret;
        }
        */


        public static int AddJob(int officeId, string faultIdString ,JobType jobType,string memo) 
        {
            //int ret;
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("AddJob", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@OfficeId", officeId);
            cmd.Parameters.AddWithValue("@FaultIdString", faultIdString);
            cmd.Parameters.AddWithValue("@JobTypeId", jobType);
            cmd.Parameters.AddWithValue("@Memo", memo);
            cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.BigInt);
            cmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
            using (conn)
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            return (int)cmd.Parameters["@RETURN_VALUE"].Value;
        }

        public static List<Fault> GetAllFaults(bool IncludeVisible = false) 
        {
            List<Fault> ret = new List<Fault>();
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("GetAllFaults", conn);
            SqlDataReader reader;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@IncludeInvisible", IncludeVisible);
            using (conn)
            {
                conn.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Fault fault = new Fault();
                    fault.FaultId = (int)reader["Id"];
                    fault.FaultName = reader["FaultName"].ToString();
                    fault.Visible = (bool)reader["Visible"];
                    ret.Add(fault);
                }
            }
            return ret;
        }

        public static List<Office> GetOffices(int districtId, bool excludeMaintainced) 
        {
            List<Office> ret = new List<Office>();
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("GetOffices", conn);
            SqlDataReader reader;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@DistrictId", districtId);
            cmd.Parameters.AddWithValue("@ExcludeMaintainced", excludeMaintainced);
            using (conn)
            {
                conn.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Office office = new Office();
                    office.OfficeId = (int)reader["Id"];
                    office.DistrictId = (int)reader["DistrictId"];
                    office.OfficeName = reader["OfficeName"].ToString();
                    ret.Add(office);
                }
            }
            return ret;
        }

        public static List<Crew> GetAllCrews()
        {
            List<Crew> ret = new List<Crew>();
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("GetAllCrews", conn);
            SqlDataReader reader;
            cmd.CommandType = CommandType.StoredProcedure;
            using (conn)
            {
                conn.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    Crew crew = new Crew();
                    crew.CrewId = (int)reader["CrewId"];
                    crew.CrewName = reader["CrewName"].ToString();
                    ret.Add(crew);
                }
            }
            return ret;
        }

        public static List<District> GetDistricts(bool excludeMaintainced) 
        {
            List<District> ret = new List<District>();
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand("GetDistricts", conn);
            SqlDataReader reader;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@ExcludeMaintainced", excludeMaintainced);
            using (conn)
            {
                conn.Open();
                reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    District district = new District();
                    district.DistrictId = (int)reader["DistrictId"];
                    district.DistrictName = reader["DistrictName"].ToString();
                    ret.Add(district);
                }
            }
            return ret;
        }
    }


    public class Crew
    {
        public int CrewId { get; set; }
        public string CrewNo { get; set; }
        public string CrewName { get; set; }
        public int Permission { get; set; }
        //checktag  是啥自己都忘了

    }

    public class District
    {
        public int DistrictId { get; set; }
        public string DistrictName { get; set; }
    }

    public class Office
    {
        public int OfficeId{ get; set; }
        public int DistrictId{ get; set; }
        public string OfficeName{ get; set; }
        public string Address{ get; set; }
        public string Tel{ get; set; }
        public int CrewId{ get; set; }
        public int DelegateCrewId{ get; set; }
        public bool Maintained{ get; set; }
        public bool Available{ get; set; }

    }

    public class Fault 
    {
        public int FaultId{ get; set; }
        public string FaultName{ get; set; }
        public bool Visible { get; set; }
    }

    public class Job 
    {
        public int DistrictId{ get; set; }
        public string DistrictName{ get; set; }
        public int OfficeId{ get; set; }
        public string OfficeName{ get; set; }
        public string FaultIdString{ get; set; }
        public string FaultName{ get; set; }
        public bool NeedMaintaince{ get; set; }
        public string CallInDate{ get; set; }
        public int FormId{ get; set; }
        public string NotifyDate{ get; set; }
        public string ArriveDate { get; set; }
        public string FinishDate { get; set; }
        public int CrewId{ get; set; }
        public int DelegateCrewId { get; set; }
        public string CrewName { get; set; }
        public string DelegateCrewName { get; set; }
        public int JobStatus { get; set; }
        public string JobType { get; set; }
        public string Memo { get; set; }

    }

    public class MonthlyReportItem 
    {
        public string JobDate { get; set; }
        public string CrewName{ get; set; }
        public string Office{ get; set; }
        public string RepairType{ get; set; }
        public string ArriveTime{ get; set; }
        public string FinishtTime{ get; set; }
        public string FaultIdString{ get; set; }
        public string Faults { get; set; }
    }


}